<?php
/**
 * @description : sample db operations.
 * @class-name : RestoreModel().
 * @author : stephen.mo <stephen.mo@achievo.com>
 * @date : 2010-04-01
 */

require_once 'TVB/Config/requires.php';
require_once 'TVB/DB/requires.php';
require_once 'RestoreModelConfig.class.php';

class RestoreModel {
    public $config;
    public $reader;
    public $writer;

    //instance
    public static function getInstance() {
        static $instance = null ;
        return $instance ? ($instance) : ($instance = new RestoreModel()) ;
    }
    
    //construct
    public function __construct() {
        global $log_type_;
        $this->writer = DBConnPoolFactory::getInstance()->getConnPool(RMC::getLogsysDatabaseConfig($log_type_))->getWriteConn();
    }
    
    public function checkIsMethodExist($method) {
        $obj = self::getInstance();//new RestoreModel();
        return method_exists($obj,$method);
    }
    
    //set the video access table name.
    private function getTableNameOfVideoAccess($date=NULL) {
        $date_string = (empty($date)) ? date('Y_m') : date('Y_m',strtotime($date));
        return 'video_access_log_'.$date_string;
    }

    //set the basic table name
    static function getBasicTableNameOfVideoAccess() {
        return 'video_access_log';
    }
    
    //set the temp video access log.
    static function getTableNameOfVideoAccessLogTemp() {
        return 'video_access_log_temp';
    }

    static function getBasicTableNameOfFpVideoAccess() {
        return 'fp_video_access';
    }
    
    static function getTableNameOfFpVideoAccessTemp() {
        return 'fp_video_access_temp';
    }

    //set the sign in or out table name.
    private function getTableNameOfSignInOut() {
        return 'member_access_log';
    }

    //set the subscription table name.
    private function getTableNameOfPlanSubscription() {
        return 'subscription_log';
    }
    
    //set the fp video access table name.
    private function getTableNameOfFpVideoAccess($date=NULL) {
        $date_string = (empty($date)) ? date('Y_m') : date('Y_m',strtotime($date));
        return 'fp_video_access_'.$date_string;
    }

    //set the fp video access ws table name.
    private function getTableNameOfFpVideoAccessWs() {
        return 'fp_video_access_ws';
    }

    //set the fp page access table name. alias sign_up_log
    private function getTableNameOfFpPageAccess() {
        return 'fp_page_access';
    }

    //set the special table prefix.
    private function getTablePrefixOfVideoAccess() {
        return 'video_access_log_';
    }

    private function getTablePrefixOfFpVideoAccess() {
        return 'fp_video_access_';
    }

    //set the merge table name.
    private function getMergeTableMidfixOfVideoAccess($year) {
        $midfix = PGC::$video_access_merger_midfix;
        return self::getBasicTableNameOfVideoAccess().$midfix.$year;
    }

    private function getTiggerTableNameOfFpVideoAccess() {
        return 'fp_video_access_ws';
    }

    //get the table's real name by $name.
    private function getTableName($name=null) {
        if (empty($name)) $name = 'VideoAccess';
        $table = 'getTableNameOf'.$name;
        return $this->{$table}();
    }
    
    //get the table's prefix string by $name.
    private function getTablePrefixName($name=null) {
        if (empty($name)) $name = 'VideoAccess';
        $table = 'getTablePrefixOf'.$name;
        if ($this->checkIsMethodExist($table)) {
            return $this->{$table}();
        }
        return false;
    }
    
    //get the table's middle string by $name
    private function getMergeTableMidfixName($name,$year) {
        $table = 'getMergeTableMidfixOf'.$name;
        if ($this->checkIsMethodExist($table)) {
            return $this->{$table}($year);
        }
        return false;
    }

    //get the table's tigger 
    private function getTiggerTableName($name) {
        $table = 'getTiggerTableNameOf'.$name;
        if ($this->checkIsMethodExist($table)) {
            return $this->{$table}();
        }

        return false;
    }


    //create the table's structure
    private function setTableStructureHeader($table_name,$database_name=null) {
        $database_name = (empty($database_name)) ? PGC::PROJECT_DEFAULT_DATABASE_NAME : $database_name;
        $query = "CREATE TABLE IF NOT EXISTS `".$database_name."`.`".$table_name."` (";
        return $query;
    }

    private function setTableStructureFooter($comment,$merge=array(),$engine='InnoDB',$charset='utf8',$collate='utf8_general_ci') {
        $merge_list = (!empty($merge) && is_array($merge)) ? " INSERT_METHOD=LAST UNION=(`".implode('`,`',$merge)."`)" : '';
        $query = " ENGINE = ".$engine.
                 " DEFAULT CHARACTER SET = ".$charset.
                 " COLLATE = ".$collate.$merge_list.
                 " COMMENT='".$comment."';\n";
        return $query;
    }

    //create the missing tables 
    private function createYearMonthTable($table_prefix,$year,$month=array()) {
        if (empty($month)) {
            for($i=1;$i<=12;$i++) {
                $j = ($i<10) ? '0'.$i : $i;
                $month[] = $j;
            }
        }

        if (empty($month)) return false;
        //get the table engine.
        $default_engine = $table_prefix.'engine';
        $table_engine = PGC::$$default_engine;

        //create the month table .
        foreach($month as $key=>$val) {
            $query = '';
            $table_name = $table_prefix.$year.'_'.$val;
            $query = $this->setTableStructureHeader($table_name);
            $tmp_table_name = str_replace('_','',$table_prefix).'TableStructureBody';
            $query.= $this->{$tmp_table_name}();
            $query.= $this->setTableStructureFooter(PGC::$$table_prefix.' For Month '.$val,null,$table_engine);
            //execute.
            $ps = $this->writer->prepare($query);
            $ret = $ps->execute();
            if (!$ret) {
                $errors = $ps->errorInfo();
                //write log $errors[2];
                return false;
            }
        }
        return true;
    }
    
    
    //create the merge table.
    private function createMergeTable($table_name,$table_suffix,$year) {
        if (empty($table_name)) return false;
        $month_table = array();
        $table_prefix = $this->getTablePrefixName($table_suffix);
        for($i=1;$i<=12;$i++) {
            $j = ($i<10) ? '0'.$i : $i;
            $month_table[] = $table_prefix.$year.'_'.$j;
        }
        
        //create a merge table for every month table.
        $table_content_text = self::getBasicTableNameOfVideoAccess($table_suffix).PGC::$video_access_merger_midfix;
        $query = '';
        $query.= $this->setTableStructureHeader($table_name);
        $tmp_table_body = str_replace('_','',$table_prefix).'TableStructureBody';
        $query.= $this->{$tmp_table_body}();
        $query.= $this->setTableStructureFooter(PGC::$$table_content_text,$month_table,'MRG_MyISAM');
        $ps = $this->writer->prepare($query);
        $ret = $ps->execute();
        if (!$ret) {
            $errors = $ps->errorInfo();
            return false;
        }

        return true;
    }


    //set the video access table structure body.
    private function videoAccessLogTableStructureBody() {/*{{{*/
        $query = "\n`id` INT NOT NULL AUTO_INCREMENT ,".
                 "`client_id` INT NULL ,\n".
                 "`member_id` varchar(50) NULL ,\n".
                 "`video_id` INT NULL ,\n".
                 "`video_title` VARCHAR(32) NULL ,\n".
                 "`programme_id` INT NULL ,\n".
                 "`programme_name` VARCHAR(64) NULL ,\n".
                 "`category_id` INT NULL ,\n".
                 "`category_name` VARCHAR(32) NULL ,\n".
                 "`category_path` VARCHAR(45) NULL ,\n".
                 "`episode_id` INT NULL ,\n".
                 "`episode_num` VARCHAR(32) NULL ,\n".
                 "`client_ip` VARCHAR(15) NULL ,\n".
                 "`country_code` VARCHAR(8) NULL ,\n".
                 "`gender` CHAR(1) NULL ,\n".
                 "`age` TINYINT NULL ,\n".
                 "`age_group` TINYINT NULL COMMENT '1:<20,2: 21-25,3: 26-30, 4:31-35, 5:36-40,6: 41-45,7: 46-50,8: 50+' ,\n".
                 "`play_time` DATETIME NULL ,\n".
                 "`last_alive_time` DATETIME NULL ,\n".
                 "`duration` INT NULL ,\n".
                 "`content_type` TINYINT NULL COMMENT '1:free 2:premium ' ,\n".
                 "`content_lang` VARCHAR(8) NULL ,\n".
                 "`live` TINYINT(1) NULL COMMENT '0: vod 1: live ' ,\n".
                 "`bitrate` SMALLINT NULL ,\n".
                 "`package_id` INT NULL ,\n".
                 "`package_version` varchar(10) NULL ,\n".
                 "`last_modify_time` DATETIME NULL ,\n".
                 "PRIMARY KEY (`id`) ,\n".
                 "UNIQUE INDEX `idx_uni_mvc` (`member_id` ASC, `client_id` ASC, `play_time` ASC) ,\n".
                 "INDEX `idx_play_time` (`play_time` ASC) ,\n".
                 "INDEX `idx_package_id` (`package_id` ASC) ,\n".
                 "INDEX `idx_bitrate` (`bitrate` ASC) ,\n".
                 "INDEX `idx_age_group` (`age_group` ASC) ,\n".
                 "INDEX `idx_member_id` (`member_id`) ,\n".
                 "INDEX `idx_client_id` (`client_id`) ,\n".
                 "INDEX `idx_video_id` (`video_id`) ,\n".
                 "INDEX `idx_programme_id` (`programme_id`) ,\n".
                 "INDEX `idx_country_code` (`country_code`) ,\n".
                 "INDEX `idx_last_alive_time` (`last_alive_time`) ,\n".
                 "INDEX `idx_gender` (`gender`) ,\n".
                 "INDEX `idx_content_type` (`content_type` ASC) ) \n";
        return $query;               
    }/*}}}*/

    
    //set the footprint video access table structure.
    private function fpVideoAccessTableStructureBody() {/*{{{*/
        $query = "\n`id` INT NOT NULL AUTO_INCREMENT ,".
                 "`access_datetime` DATETIME NULL ,\n".
                 "`login_id` VARCHAR(50) NULL ,\n".
                 "`ui_lang` VARCHAR(8) NULL ,\n".
                 "`client_ip` VARCHAR(15) NULL ,\n".
                 "`country_code` VARCHAR(8) NULL ,\n".
                 "`url` VARCHAR(64) NULL ,\n".
                 "`video_id` INT NULL ,\n".
                 "`video_title` VARCHAR(128) NULL ,\n".
                 "`video_lang` VARCHAR(8) NULL ,\n".
                 "`programme_id` INT NULL ,\n".
                 "`category_id` INT NULL ,\n".
                 "`category_path` VARCHAR(45) NULL COMMENT 'e.g. drama , music' ,\n".
                 "`online_datetime` DATETIME NULL ,\n".
                 "`video_type` TINYINT NULL COMMENT '1:free, 2: premium ' ,\n".
                 "`count_type` TINYINT NULL COMMENT '1: view 2: favour ' ,\n".
                 "`user_agent` VARCHAR(255) DEFAULT NULL,\n".
                 "`browser` VARCHAR(50) DEFAULT NULL,\n".
                 "`os` VARCHAR(50) DEFAULT NULL ,\n".
                 "PRIMARY KEY (`id`) ,\n".
                 "INDEX `idx_access_time` (`access_datetime` ASC) ,\n".
                 "INDEX `idx_video_id` (`video_id` ASC) ,\n".
                 "INDEX `idx_category_id` (`category_id` ASC) ,\n".
                 "INDEX `idx_video_type` (`video_type` ASC) ,\n".
                 "INDEX `idx_count_type` (`count_type` ASC) )\n";
        return $query;
    }/*}}}*/


    /**
     * @description : define the table's fields list.
     * @param string $table : table's name.
     * @return array.
     */
    static protected function getFieldsAndCondition($table) {
        //check and select the record fields
        $result = array(
                    'VideoAccess'=>array(
                        'fields'=>'id,UNIX_TIMESTAMP(last_alive_time) as last_time',
                        'condition'=>'client_id=:client_id AND video_id=:video_id AND member_id=:member_id ',
                        'bind'=>array(':client_id'=>1,':video_id'=>13,':member_id'=>2)
                    ),
                    'SignInOut'=>array(
                        'fields'=>'id,UNIX_TIMESTAMP(login_time) as last_time',
                        'condition'=>'login_id=:login_id AND rand_code=:rand_code ',
                        'bind'=>array(':login_id'=>1,':rand_code'=>3)
                    ),
                    'PlanSubscription'=>array(
                        'fields'=>'id,UNIX_TIMESTAMP(order_datetime) as last_time',
                        'condition'=>'order_datetime=:order_datetime AND login_id=:login_id AND package_id=:package_id AND plan_status=:plan_status AND type=:type ',
                        'bind'=>array(':order_datetime'=>0,':login_id'=>1,':package_id'=>5,':plan_status'=>7,':type'=>8)
                    ),
                    'FpVideoAccess'=>array(
                        'fields'=>'id,UNIX_TIMESTAMP(access_datetime) as last_time',
                        'condition'=>'access_datetime=:access_datetime AND login_id=:login_id AND video_id=:video_id AND video_lang=:video_lang AND category_id=:category_id AND video_type=:video_type AND count_type=:count_type ',
                        'bind'=>array(':access_datetime'=>0,':login_id'=>1,':video_id'=>6,':video_lang'=>8,':category_id'=>10,':video_type'=>13,':count_type'=>14)
                    ),
                    'FpPageAccess'=>array(
                        'fields'=>'id,UNIX_TIMESTAMP(access_datetime) as last_time',
                        'condition'=>'access_datetime=:access_datetime AND login_id=:login_id AND page_type=:page_type AND step=:step AND client_ip=:client_ip ',
                        'bind'=>array(':access_datetime'=>0,':login_id'=>1,':page_type'=>6,':step'=>7,':client_ip'=>3)
                    ),
                    'FpVideoAccessWs'=>array(
                        'fields'=>'id,UNIX_TIMESTAMP(last_modify_time) as last_time',
                        'condition'=>'video_id=:video_id AND programme_id=:programme_id',
                        'bind'=>array(':video_id'=>6,':programme_id'=>9),
                    ),
                 );

        return (isset($result[$table])) ? $result[$table] : array();
    }


    /**
     * @description : check the record is exist.
     * @param string $table : table's name.
     * @param array  $data  : the table's records.
     * @return mixd
     */
    public function checkIsExistRecord($table,$data) {
        $setting = self::getFieldsAndCondition($table);
        if (empty($setting)) return -1;
        switch($table) {
            case 'VideoAccess':
                $table_name = self::getTableNameOfVideoAccessLogTemp();
                break;
            case 'FpVideoAccess':
                $table_name = self::getTableNameOfFpVideoAccessTemp();
                break;
            default:
                $table_name = $this->getTableName($table);
        }

        $query = "SELECT ".$setting['fields']." FROM ".$table_name.
                 " WHERE ".$setting['condition'];
        $ps = $this->writer->prepare($query);
        foreach((array)$setting['bind'] as $key=>$value) {
            $ps->BindValue("$key",$data[$value]);
        }

        $ret = $ps->execute();
        if (!$ret) {
	        //print_r($ps->errorInfo(),1);
            //write log here.
            return false;
        }

        $result = $ps->fetch(PDO::FETCH_ASSOC);
        return ($ps->rowCount()>0) ? intval($result['last_time']) : false;
    }
    

    /**
     * @description : check is exist month table. eg:table-name_YYYY_MM
     * @param string $table_prefix : table name's prefix string.
     * @param string $year : year string ,eg:2010,2009.
     * @return bool
     */
    public function checkIsExistTable($table_prefix,$year) {
        if (empty($table_prefix)) return false;

        $query = "show tables like '".$table_prefix.$year."%'; ";
        $ps = $this->writer->prepare($query);
        $ret = $ps->execute();
        if (!$ret) {
            //write log.
            return false;
        }

        //create all table.
        if ($ps->rowCount()<0) {
            $result = $this->createYearMonthTable($table_prefix,$year);
            return $result;
        }
        
        //get result list.
        $result = $ps->fetchAll(PDO::FETCH_ASSOC);
        $table_name_list = array();
        foreach($result as $key=>$val) {
            foreach($val as $key2=>$val2) {
                $table_name_list[] = $val2;
            }
        }
        
        //create missing tables other month table.
        if (empty($table_prefix)) return false;
        $temp_array = array();
        for ($i=1;$i<=12;$i++) {
            $j = ($i<10) ? '0'.$i : $i;
            $table_name = $table_prefix.$year.'_'.$j;
            if (!in_array($table_name,$table_name_list)) {
                $temp_array[] = $j;
            }
        }

        return (empty($temp_array)) ? true : $this->createYearMonthTable($table_prefix,$year,$temp_array);
    }


    /**
     * @description : check is exist merge table for all month table.
     * @param string $table_suffix : 
     * @param string $year
     * @return bool
     */
    public function checkIsExistMergeTable($table_suffix,$year) {
        if (empty($table_suffix)) return false;
        $table_name = $this->getMergeTableMidfixName($table_suffix,$year);

        $query = "show tables like '".$table_name."';";
        $ps = $this->writer->prepare($query);
        $ret = $ps->execute();
        if (!$ret) {
            //write log
            return false;
        }
       
        if ($ps->rowCount()<=0) {
            return $this->createMergeTable($table_name,$table_suffix,$year);
        }

        return true;
    }


    /**
     * @description : create a trigger with table.
     * @param string $table_suffix :
     * @return bool
     */
    public function createTriggerDefault($table_suffix) {
        return true;
        if (empty($table_suffix)) return false;
        $trigger_table = $this->getTiggerTableName($table_suffix);
        $table_prefix = $this->getTablePrefixName($table_suffix);
        $database_name = PGC::PROJECT_DEFAULT_DATABASE_NAME;
        $year = date('Y');
        $month = date('m');

        $temp_array = array();
        for ($i=1;$i<=12;$i++) {
            $j = ($i<10) ? '0'.$i : $i;
            $key = $year.'_'.$j;
            $table_name = $table_prefix.$key;
            $trigger_name = $trigger_table.'_trigger_'.$key;
            $temp_array[$trigger_name] = $table_name;
        }
        
        $temp_current_trigger = array();
        $query = "SHOW TRIGGERS";
        $ps = $this->writer->prepare($query);
        $ret = $ps->execute();
        $result = $ps->fetchAll(PDO::FETCH_ASSOC);
        foreach($result as $key=>$value) {
            $temp_current_trigger[] = $value['Trigger'];
        }

        foreach($temp_array as $key=>$value) {
            if (in_array($key,$temp_current_trigger)) continue;
            $query = "USE `".$database_name."`;\n".
                     "DELIMITER //\n".
                     "CREATE TRIGGER ".$key." AFTER INSERT ON ".$value." \n".
                     "FOR EACH ROW BEGIN\n".
                     "IF (SELECT count(video_id) as counter FROM ".$trigger_table." WHERE video_id = NEW.video_id)=0 THEN\n".
                     "INSERT INTO ".$trigger_table." (`video_id`,`programme_id`,`viewing`,`favour`,`last_modify_time`) VALUES (NEW.video_id,NEW.programme_id,1,1,NOW());\n".
                     "ELSE\n".
                     "UPDATE  ".$trigger_table." SET viewing = viewing + 1, favour=favour+1, last_modify_time =now() WHERE video_id = NEW.video_id;\n".
                     "END IF;\n".
                     "END\n".
                     "//\n".
                     "DELIMITER ;\n";
            echo $query."\n\n\n";
        }

        die();
        
        return true;
    }


    /**
     * @description : auto Splite table ,if doesn't exist then create it.
     * @param string $table_suffix : table name's suffix. eg:VideoAccess,SignInOut...
     * @return bool
     */
    public function autoSplitTable($table_suffix) {
        //check the table is exist.
        $table_prefix = $this->getTablePrefixName($table_suffix);
        $check_result = $this->checkIsExistTable($table_prefix,date('Y'));
        //check the merge table is exist
        switch($table_suffix) {
            case 'VideoAccessLog':
                //$check_result = ($check_result) ? $this->checkIsExistMergeTable($table_suffix,date('Y')) : false;
                break;
            case 'FpVideoAccess':
                //$check_result = ($check_result) ? $this->createTriggerDefault($table_suffix) : false;
                break;
            default:
                break;
        }
        return (!$check_result) ? false : true;
    }
    
    static public function getBrowserList() {
        $list = array();
        $config = ConfigReaderFactory::getInstance()->getConfigReader(PGC::XML_FILE_OF_USER_AGENT_BROWSER)->getConfig();
        foreach($config->browsers as $key=>$value) {
            foreach($value->browser as $b_k=>$b_v) {
                $name = (string)$b_v->attributes()->name;
				$combina = (!empty($b_v->attributes()->combina)) ? (string)$b_v->attributes()->combina : 'OR';
                $pattern = array();
                foreach($b_v as $p_k=>$p_v) {
                    $pattern[] = (string)$p_v->attributes()->value;
                }
				$list[$name]['combina'] = $combina;
                $list[$name]['pattern'] = $pattern;
            }
        }
        return $list;
    }

    static public function getOSList() {
        $list = array();
        $config = ConfigReaderFactory::getInstance()->getConfigReader(PGC::XML_FILE_OF_USER_AGENT_OS)->getConfig();
        foreach($config->items as $key=>$value) {
            foreach($value as $o_k=>$o_v) {
				$name = (string)$o_v->attributes()->name;
				$combina = (!empty($o_v->attributes()->combina)) ? (string)$o_v->attributes()->combina : 'OR';
				$pattern = array();
				foreach($o_v as $p_k=>$p_v) {
					$pattern[] = (string)$p_v->attributes()->value;
				}
				$list[$name]['combina'] = $combina;
				$list[$name]['pattern'] = $pattern;
			}
        }
        return $list;
    }

    /**
     * @description : pattern the User Agent's OS & Browser.
     * @param string $user_agent : user agent string.
     * @return array('browser'=>'','os'=>'xx)
     */
    static public function patternOSAndBrowser($user_agent,$browser_pattern_list,$os_pattern_list) {
        //pattern the Browser & OS
        $browser = null;
        $pattern_flag = false;
        foreach($browser_pattern_list as $name_k=>$pattern_v) {
            if ($pattern_flag) break;
            $combina = (strtoupper($pattern_v['combina'])=='AND') ? true : false;
            foreach($pattern_v['pattern'] as $p_k=>$p_list) {
                if ($combina) {
                    if (eregi($p_list,$user_agent)==false) {
                        $browser = null;
                        break;
                    }
                    $browser = $name_k;
                }
                else {
                    if (eregi($p_list,$user_agent)==false) continue;
                    $browser = $name_k;
                    $pattern_flag = true;
                    break;
                }
            }
        }
        if (empty($browser)) $browser = 'Other';

        $os = null;
        $pattern_flag = false;
        foreach($os_pattern_list as $name_k=>$pattern_v) {
            if ($pattern_flag) break;
            $combina = (strtoupper($pattern_v['combina'])=='AND') ? true : false;
            foreach($pattern_v['pattern'] as $p_k=>$p_list) {
                if ($combina) {
                    if (eregi($p_list,$user_agent)==false) {
                        $os = null;
                        break;
                    }
                    $os = $name_k;
                }
                else {
                    if (eregi($p_list,$user_agent)==false) continue;
                    $os = $name_k;
                    $pattern_flag = true;
                    break;
                }
            }
        }
        if (empty($os)) $os = 'Other';
        return array('browser'=>$browser,'os'=>$os);
    }

    /**
     * @description : update the video_access_log with the data by conditions.
     * @param int $last_time : the pre-record's last alive time.It's unixtimestamp.
     * @para array $data : one line of the log data.
     * @return bool
     */
    public function updateRecordOfVideoAccess($last_time,$data) {/*{{{*/
        //datetime(0)|client_id(1)|member_id(2)|event(3)|client_ip(4)|bitrate(5)|country_code(6)|
        //gender(7)|age(8)|pid(9)|pname(10)|episode_id(11)|episode_num(12)|video_id(13)|video_title(14)|
        //category_id(15)|category_name(16)|category_path(17)|content_type(18)|package_id(19)|lang(20)|live(21)|package-version(22)
        $query = "UPDATE ".$this->getTableNameOfVideoAccess().
                 " SET bitrate=:bitrate,last_modify_time=:last_modify_time,last_alive_time=:last_alive_time,duration=duration+:duration ".
                 " WHERE client_id=:client_id AND member_id=:member_id AND video_id=:video_id AND last_alive_time<:current_time ";
        $ps = $this->writer->prepare($query);
        $ps->BindValue(':last_modify_time',$data[0]);
        $ps->BindValue(':last_alive_time',$data[0]);
        $ps->BindValue(':bitrate',$data[5]);
        $event = strtolower($data[1]);
        switch($event) {
            case 'unpause':
            case 'play':
                $ps->BindValue(':duration',0);
                break;
            default://first_play|pause|stop|alive
                $ps->BindValue(':duration',(strtotime($data[0])-intval($last_time)));
        }
        $ps->BindValue(':client_id',$data[1]);
        $ps->BindValue(':member_id',$data[2]);
        $ps->BindValue(':video_id',$data[13]);
        $ps->BindValue(':current_time',$data[0]);
        $ret = $ps->execute();
        if (!$ret) {
            //write log.
            return false;
        }

        //update to video_access_log_temp;
        //Must keep the table structure consistent 
        $query = "UPDATE ".$this->getTableNameOfVideoAccessLogTemp().
                 " SET bitrate=:bitrate,last_modify_time=:last_modify_time,last_alive_time=:last_alive_time,duration=duration+:duration ".
                 " WHERE client_id=:client_id AND member_id=:member_id AND video_id=:video_id AND last_alive_time<:current_time ";
        $ps = $this->writer->prepare($query);
        $ps->BindValue(':last_modify_time',$data[0]);
        $ps->BindValue(':last_alive_time',$data[0]);
        $ps->BindValue(':bitrate',$data[5]);
        $event = strtolower($data[1]);
        switch($event) {
            case 'unpause':
            case 'play':
                $ps->BindValue(':duration',0);
                break;
            default://first_play|pause|stop|alive
                $ps->BindValue(':duration',(strtotime($data[0])-intval($last_time)));
        }
        $ps->BindValue(':client_id',$data[1]);
        $ps->BindValue(':member_id',$data[2]);
        $ps->BindValue(':video_id',$data[13]);
        $ps->BindValue(':current_time',$data[0]);
        $ret = $ps->execute();
        if (!$ret) {
            //write log.
            return false;
        }

        return true;

    }/*}}}*/
    

    /**
     * @description : insert a new record to video_access_log.
     * @param array $data : one line of the log data.
     * @return bool
     */
    public function insertRecordOfVideoAccess($data) {/*{{{*/
        //datetime(0)|client_id(1)|member_id(2)|event(3)|client_ip(4)|bitrate(5)|country_code(6)|
        //gender(7)|age(8)|pid(9)|pname(10)|episode_id(11)|episode_num(12)|video_id(13)|video_title(14)|
        //category_id(15)|category_name(16)|category_path(17)|content_type(18)|package_id(19)|lang(20)|live(21)|package-version(22)
        //2010-05-07 14:12:01|123456|stephen.mo|alive|10.123.23.12|200|HK|M|27|100084|宫心計|100291|14|21311|三好進宮|1|劇集|drama|1|0|1|0|1.0.0
        $query = "INSERT INTO ".$this->getTableNameOfVideoAccess($data[0]).
                 " (id,client_id,member_id,video_id,video_title,bitrate,programme_id,programme_name,".
                 "  category_id,category_name,category_path,episode_id,episode_num,client_ip,country_code,".
                 "  gender,age,age_group,content_lang,play_time,last_alive_time,duration,content_type,package_id,".
                 "  package_version,last_modify_time,live) ".
                 " VALUES ".
                 " (0,:client_id,:member_id,:video_id,:video_title,:bitrate,:programme_id,:programme_name,".
                 "  :category_id,:category_name,:category_path,:episode_id,:episode_num,:client_ip,:country_code,".
                 "  :gender,:age,:age_group,:lang,:play_time,:last_alive_time,:duration,:content_type,:package_id,".
                 ":package_version,:last_modify_time,:live) ";
        $ps = $this->writer->prepare($query);
        $ps->BindValue(':member_id',$data[2]);
        $ps->BindValue(':client_id',(int)$data[1]);
        $ps->BindValue(':video_id',(int)$data[13]);
        $ps->BindValue(':video_title',$data[14]);
        $ps->BindValue(':bitrate',$data[5]);
        $ps->BindValue(':programme_id',(int)$data[9]);
        $ps->BindValue(':programme_name',$data[10]);
        $ps->BindValue(':category_id',(int)$data[15]);
        $ps->BindValue(':category_name',$data[16]);
        $ps->BindValue(':category_path',$data[17]);
        $ps->BindValue(':episode_id',(int)$data[11]);
        $ps->BindValue(':episode_num',$data[12]);
        $ps->BindValue(':client_ip',$data[4]);
        $ps->BindValue(':country_code',$data[6]);
        $ps->BindValue(':gender',$data[7]);
        $ps->BindValue(':age',intval($data[8]));
        $ps->BindValue(':age_group',RMC::getAgeGroupId((int)$data[8]));
        $ps->BindValue(':lang',trim($data[20]));
        $ps->BindValue(':play_time',$data[0]);
        $ps->BindValue(':last_alive_time',$data[0]);
        $ps->BindValue(':duration',0);
        $ps->BindValue(':content_type',(int)$data[18]);
        $ps->BindValue(':package_id',(int)$data[19]);
        $ps->BindValue(':last_modify_time',$data[0]);
        $ps->BindValue(':live',$data[21]);
        $ps->BindValue(':package_version',$data[22]);

        $ret = $ps->execute();
        if (!$ret) {
            //write log.
            return false;
        }

        //Insert to video_access_log_temp;
        //Must keep the table structure consistent        
        $query = "INSERT INTO ".$this->getTableNameOfVideoAccessLogTemp().
                 " (id,client_id,member_id,video_id,video_title,bitrate,programme_id,programme_name,".
                 "  category_id,category_name,category_path,episode_id,episode_num,client_ip,country_code,".
                 "  gender,age,age_group,content_lang,play_time,last_alive_time,duration,content_type,package_id,".
                 "  package_version,last_modify_time,live) ".
                 " VALUES ".
                 " (0,:client_id,:member_id,:video_id,:video_title,:bitrate,:programme_id,:programme_name,".
                 "  :category_id,:category_name,:category_path,:episode_id,:episode_num,:client_ip,:country_code,".
                 "  :gender,:age,:age_group,:lang,:play_time,:last_alive_time,:duration,:content_type,:package_id,".
                 ":package_version,:last_modify_time,:live) ";
        $ps = $this->writer->prepare($query);
        $ps->BindValue(':member_id',$data[2]);
        $ps->BindValue(':client_id',(int)$data[1]);
        $ps->BindValue(':video_id',(int)$data[13]);
        $ps->BindValue(':video_title',$data[14]);
        $ps->BindValue(':bitrate',$data[5]);
        $ps->BindValue(':programme_id',(int)$data[9]);
        $ps->BindValue(':programme_name',$data[10]);
        $ps->BindValue(':category_id',(int)$data[15]);
        $ps->BindValue(':category_name',$data[16]);
        $ps->BindValue(':category_path',$data[17]);
        $ps->BindValue(':episode_id',(int)$data[11]);
        $ps->BindValue(':episode_num',$data[12]);
        $ps->BindValue(':client_ip',$data[4]);
        $ps->BindValue(':country_code',$data[6]);
        $ps->BindValue(':gender',$data[7]);
        $ps->BindValue(':age',intval($data[8]));
        $ps->BindValue(':age_group',RMC::getAgeGroupId((int)$data[8]));
        $ps->BindValue(':lang',trim($data[20]));
        $ps->BindValue(':play_time',$data[0]);
        $ps->BindValue(':last_alive_time',$data[0]);
        $ps->BindValue(':duration',0);
        $ps->BindValue(':content_type',(int)$data[18]);
        $ps->BindValue(':package_id',(int)$data[19]);
        $ps->BindValue(':last_modify_time',$data[0]);
        $ps->BindValue(':live',$data[21]);
        $ps->BindValue(':package_version',$data[22]);

        $ret = $ps->execute();
        if (!$ret) {
            //write log.
            return false;
        }

        return true;

    }/*}}}*/

    
    /**
     * @description : insert a new record to subscription-log
     * @param array $data : one line of log data.
     * @return bool
     */
    public function insertRecordOfPlanSubscription($data) {
        //datetime(0)|member-id(1)|country_code(2)|gender(3)|age(4)|package-id(5)|package-version(6)|play-status(7)|plan-type(8)|order_ref(9)|order_plan_id(10)
        $query = "INSERT INTO ".$this->getTableNameOfPlanSubscription(). 
                 " (id,order_datetime,login_id,country_code,gender,age,age_group,package_id,package_version,plan_status,type,process_time,order_ref,order_id) ".
                 "VALUES".
                 " (0,:order_datetime,:login_id,:country_code,:gender,:age,:age_group,:package_id,:package_version,:plan_status,:type,:process_time,:order_ref,:order_plan_id) ";
        $ps = $this->writer->prepare($query);
        $ps->BindValue(':order_datetime',$data[0]);
        $ps->BindValue(':login_id',$data[1]);
        $ps->BindValue(':country_code',$data[2]);
        $ps->BindValue(':gender',$data[3]);
        $ps->BindValue(':age',$data[4]);
        $ps->BindValue(':age_group',RMC::getAgeGroupId((int)$data[4]));
        $ps->BindValue(':package_id',$data[5]);
        $ps->BindValue(':package_version',$data[6]);
        $ps->BindValue(':plan_status',$data[7]);
        $ps->BindValue(':type',$data[8]);
        $ps->BindValue(':process_time',$data[0]);
        $ps->BindValue(':order_ref',$data[9]);
        $ps->BindValue(':order_plan_id',$data[10]);
        $ret = $ps->execute();
        if (!$ret) {
            //write log
            //print_r($ps->errorInfo());
            return false;
        }

        return true;
    }
    
    /**
     * @description : insert a new record to fp video access
     * @param array $data : one line of log data.
     * @return bool
     */
    public function insertRecordOfFpVideoAccess($data,$browser_pattern,$os_pattern) {
        //datetime(0)|member_id(1)|ui-lang(2)|client-ip(3)|country-code(4)|url(5)|video-id(6)|video-title(7)|video-lang(8)|programme-id(9)|category-id(10)|category-path(11)|online-datetime(12)|video-type(13)|count-type(14)|user-agent(15)|browser(16)|os(17)
        //2010-05-07 14:51:24|stephen.mo|1|202.123.22.112|HK|http://www.tvb.com/xxxx|12312|富貴門之二|1|100012|1|drama|1|1|Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.9) Gecko/20100315 Firefox/3.5.9
        $query = "INSERT INTO ".$this->getTableNameOfFpVideoAccess($data[0]).
                 " (id,access_datetime,login_id,ui_lang,client_ip,country_code,url,video_id,video_title,video_lang,programme_id,category_id,category_path,online_datetime,video_type,count_type,user_agent,browser,os) ".
                 "VALUES".
                 " (0,:access_datetime,:login_id,:ui_lang,:client_ip,:country_code,:url,:video_id,:video_title,:video_lang,:programme_id,:category_id,:category_path,:online_datetime,:video_type,:count_type,:user_agent,:browser,:os) ";
        $ps = $this->writer->prepare($query);
        $ps->BindValue(':access_datetime',$data[0]);
        $ps->BindValue(':login_id',$data[1]);
        $ps->BindValue(':ui_lang',$data[2]);
        $ps->BindValue(':client_ip',$data[3]);
        $ps->BindValue(':country_code',$data[4]);
        $ps->BindValue(':url',$data[5]);
        $ps->BindValue(':video_id',$data[6]);
        $ps->BindValue(':video_title',$data[7]);
        $ps->BindValue(':video_lang',$data[8]);
        $ps->BindValue(':programme_id',$data[9]);
        $ps->BindValue(':category_id',$data[10]);
        $ps->BindValue(':category_path',$data[11]);
        $ps->BindValue(':online_datetime',$data[12]);
        $ps->BindValue(':video_type',$data[13]);
        $ps->BindValue(':count_type',$data[14]);
        $ps->BindValue(':user_agent',$data[15]);
        
        //get user agent's os & browser.
        $os_browser_data = self::patternOSAndBrowser($data[15],$browser_pattern,$os_pattern);
        $ps->BindValue(':browser',$os_browser_data['browser']);
        $ps->BindValue(':os',$os_browser_data['os']);
        $ret = $ps->execute();
        if (!$ret) {
            //write log
            //print_r($ps->errorInfo());
            return false;
        }

        //Insert to fp_video_access_temp;
        //Must keep the table structure consistent
        $query = "INSERT INTO ".$this->getTableNameOfFpVideoAccessTemp().
                 " (id,access_datetime,login_id,ui_lang,client_ip,country_code,url,video_id,video_title,video_lang,programme_id,category_id,category_path,online_datetime,video_type,count_type) ".
                 "VALUES".
                 " (0,:access_datetime,:login_id,:ui_lang,:client_ip,:country_code,:url,:video_id,:video_title,:video_lang,:programme_id,:category_id,:category_path,:online_datetime,:video_type,:count_type) ";
        $ps = $this->writer->prepare($query);
        $ps->BindValue(':access_datetime',$data[0]);
        $ps->BindValue(':login_id',$data[1]);
        $ps->BindValue(':ui_lang',$data[2]);
        $ps->BindValue(':client_ip',$data[3]);
        $ps->BindValue(':country_code',$data[4]);
        $ps->BindValue(':url',$data[5]);
        $ps->BindValue(':video_id',$data[6]);
        $ps->BindValue(':video_title',$data[7]);
        $ps->BindValue(':video_lang',$data[8]);
        $ps->BindValue(':programme_id',$data[9]);
        $ps->BindValue(':category_id',$data[10]);
        $ps->BindValue(':category_path',$data[11]);
        $ps->BindValue(':online_datetime',$data[12]);
        $ps->BindValue(':video_type',$data[13]);
        $ps->BindValue(':count_type',$data[14]);
        $ps->BindValue(':user_agent',$data[15]);
        $ret = $ps->execute();
        if (!$ret) {
            //write log
            //print_r($ps->errorInfo());
            return false;
        }

        return true;
    }
    
    /**
     * @description : insert a new record to fp_video_access_ws
     * @param array $data : one line of log data.
     * @return bool
     */
    public function insertReocrdOfFpVideoAccessWS($data) {
        //insert new record to fp_video_access_ws;
        $query = "INSERT INTO ".$this->getTableNameOfFpVideoAccessWs().
                 " (id,video_id,programme_id,viewing,favour,last_modify_time) ".
                 " VALUES ".
                 " (0,:video_id,:programme_id,:viewing,:favour,now()) ";
        $ps = $this->writer->prepare($query);
        $ps->BindValue(':video_id',$data[6]);
        $ps->BindValue(':programme_id',$data[9]);
        $ps->BindValue(':viewing',($data[14]==1) ? 1 : 0);
        $ps->BindValue(':favour',($data[14]==2) ? 1 : 0);
        $ret = $ps->execute();
        if (!$ret) {
            //write log
            return false;
        }

        return true;
    }
    
    /**
     * @description : update a record by video_id & programme_id
     * @param array $data : one line of log data.
     * @return bool
     */
    public function updateRecordOfFpVideoAccessWS($data) {
        $update_field = ($data[14]==1) ? ',viewing=viewing+1' : ',favour=favour+1';
        $query = "UPDATE ".$this->getTableNameOfFpVideoAccessWs().
                 " SET last_modify_time=now() ".$update_field.
                 " WHERE video_id=:video_id AND programme_id=:programme_id ";
        $ps = $this->writer->prepare($query);
        $ps->BindValue(':video_id',$data[6]);
        $ps->BindValue(':programme_id',$data[9]);
        $ret = $ps->execute();
        if (!$ret) {
            //write log
            return false;
        }
        
        return true;
    }
    


    /**
     * @description : insert a new record to fp page access
     * @param array $data : one line of log data.
     * @return bool
     */
    public function insertRecordOfFpPageAccess($data) {
        //datetime|member_id|ui-lang|client-ip|country-code|url|page-type|step|unique
        //2010-04-20 19:44:55|guest|null|220.231.152.66|HK|http://member.tvb.com/tracker/tracker.html|1|1|NULL
        $query = "INSERT INTO ".$this->getTableNameOfFpPageAccess().
                 " (id,access_datetime,login_id,ui_lang,client_ip,country_code,url,page_type,step,unique_id) ".
                 "VALUES".
                 " (0,:access_datetime,:login_id,:ui_lang,:client_ip,:country_code,:url,:page_type,:step,:unique_id) ";
        $ps = $this->writer->prepare($query);
        $ps->BindValue(':access_datetime',$data[0]);
        $ps->BindValue(':login_id',$data[1]);
        $ps->BindValue(':ui_lang',$data[2]);
        $ps->BindValue(':client_ip',$data[3]);
        $ps->BindValue(':country_code',$data[4]);
        $ps->BindValue(':url',$data[5]);
        $ps->BindValue(':page_type',$data[6]);
        $ps->BindValue(':step',$data[7]);
        $ps->BindValue(':unique_id',$data[8]);
        $ret = $ps->execute();
        if (!$ret) {
            //write log
            print_r($ps->errorInfo());
            return false;
        }

        return true;
    }


    /**
     * @description : update the member_access_log with data by conditions.
     * @param int $last_time : the last alive time .unixtimestamp
     * @param array $data : one line of the log data.
     * @return bool
     */
    public function updateRecordOfSignInOut($last_time,$data) {
        if (trim($data[2])=='login') return -1;
        //2010-05-06 17:23:44|stephen.mo|login|31231|123.12.133.10|tvb.com|3600|HK|M|27|1|tw
        $query = "UPDATE ".$this->getTableNameOfSignInOut().
                 " SET logout_time=:logout_time,duration=:duration,logout_url=:logout_url ".
                 " WHERE login_id=:login_id AND rand_code=:rand_code ";
        $ps = $this->writer->prepare($query);
        $ps->BindValue(':logout_time',$data[0]);
        $ps->BindValue(':duration',(int)$data[6]);
        $ps->BindValue(':logout_url',$data[5]);
        $ps->BindValue(':login_id',$data[1]);
        $ps->BindValue(':rand_code',$data[3]);
        $ret = $ps->execute();
        if (!$ret) {
            //write log
            return false;
        }

        return true;
    }

    
    /**
     * @description : insert a new record to member_access_log.
     * @param array $data : one line of the log data.
     * @return bool
     */
    public function insertRecordOfSignInOut($data) {
        if (trim($data[2])=='logout') return -1;
        //2010-05-06 17:23:44|stephen.mo|login|31231|123.12.133.10|tvb.com|3600|HK|M|27|1|tw
        $query = "INSERT INTO ".$this->getTableNameOfSignInOut().
                 " (id,login_id,login_time,rand_code,ip,country,login_url,duration,create_time,type) ".
                 " VALUES ".
                 " (0,:login_id,:login_time,:rand_code,:client_ip,:country,:login_url,:duration,:create_time,:type) ";
        $ps = $this->writer->prepare($query);
        $ps->BindValue(':login_id',$data[1]);
        $ps->BindValue(':login_time',$data[0]);
        $ps->BindValue(':rand_code',$data[3]);
        $ps->BindValue(':client_ip',$data[4]);
        $ps->BindValue(':country',$data[7]);
        $ps->BindValue(':login_url',$data[5]);
        $ps->BindValue(':duration',0);
        $ps->BindValue(':create_time',$data[0]);
        $ps->BindValue(':type',$data[10]);
        $ret = $ps->execute();
        if (!$ret) {
            //write log
            return false;
        }

        return true;
    }








}



?>
